﻿using System;
using DMS.Base;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;

namespace DMS.Business.Modules.Knowledge
{
    public class KnowledgeService
    {
        public static int CountKnowledge(string condition)
        {
            string countSql = String.Format(@"select count(*) num
	                                            from SYS_Knowledges n 
                                            where n.IsDeleted = 0                                            
                                            {0}", condition);
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(countSql);
            DataSet ds = DBHelper.Instance.ExecuteDataSet(command);
            return Convert.ToInt32(ds.Tables[0].Rows[0]["num"]);
        }

        public static System.Data.DataSet ListKnowledge(int pageSize, int pageIndex, int total, string condition)
        {
            string sql = String.Format(@"select *
	                                        from SYS_Knowledges n 
                                        where n.IsDeleted = 0
                                        {0} order by publishTime desc", condition);
            SqlCommand command = new SqlCommand(sql, new SqlConnection(DBHelper.Instance.ConnectionString));
            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            dataAdapter.Fill(ds, pageIndex - 1, pageSize, "Table1");
            return ds;
        }

       



        public static void DeleteKnowledge(string id)
        {
            string sql = @"update SYS_Knowledges set IsDeleted = 1 where KnowledgeID = @KnowledgeID";
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(sql);
            command.Parameters.Add(DBHelper.CreateParamer("KnowledgeID", id));
            DBHelper.Instance.ExecuteNonQuery(command);            
        }

        public static CommonKnowledge GetKnowledge(string id)
        {
            string sql = @"select * from SYS_Knowledges where KnowledgeID = @KnowledgeID";
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(sql);
            command.Parameters.Add(DBHelper.CreateParamer("KnowledgeID", id));
            DataSet ds = DBHelper.Instance.ExecuteDataSet(command);
            DataRow dr = ds.Tables[0].Rows[0];
            CommonKnowledge knowledge = FillContent(dr);
            return knowledge;
        }

        public static void UpdateKnowledge(CommonKnowledge knowledge)
        {
            string sql = @"UPDATE [SYS_Knowledges]
                               SET [Content] = @Content
                                  ,[Title] = @Title
                                  ,[Summary] = @Summary    
                                  ,[PictureUrl] = @PictureUrl                                    
                                  ,[Publisher] = @Publisher
                                  ,[PublisherID] = @PublisherID
                                  ,[PublishTime] = @PublishTime
                             WHERE [KnowledgeID] = @KnowledgeID";
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(sql);
            command.Parameters.Add(DBHelper.CreateParamer("Content", DBHelper.getValue(knowledge.Content)));
            command.Parameters.Add(DBHelper.CreateParamer("Title", DBHelper.getValue(knowledge.Title)));
            command.Parameters.Add(DBHelper.CreateParamer("Summary", DBHelper.getValue(knowledge.Summary)));
            command.Parameters.Add(DBHelper.CreateParamer("PictureUrl", DBHelper.getValue(knowledge.PictureUrl)));
            command.Parameters.Add(DBHelper.CreateParamer("Publisher", DBHelper.getValue(knowledge.Publisher)));
            command.Parameters.Add(DBHelper.CreateParamer("PublisherID", DBHelper.getValue(knowledge.PublisherID)));
            command.Parameters.Add(DBHelper.CreateParamer("PublishTime", DBHelper.getValue(knowledge.PublishTime)));
            command.Parameters.Add(DBHelper.CreateParamer("KnowledgeID", DBHelper.getValue(knowledge.KnowledgeID)));
            DBHelper.Instance.ExecuteNonQuery(command);
        }

        public static void SaveKnowledge(CommonKnowledge knowledge)
        {
            string sql = @"INSERT INTO [SYS_Knowledges]
                                   ([KnowledgeID]
                                   ,[Content]
                                   ,[Title]
                                   ,[Summary]
                                   ,[PictureUrl]
                                   ,[Publisher]
                                   ,[PublisherID]
                                   ,[PublishTime]
                                   ,[IsDeleted])
                             VALUES
                                   (NEWID()
                                   ,@Content
                                   ,@Title
                                   ,@Summary
                                   ,@PictureUrl
                                   ,@Publisher
                                   ,@PublisherID
                                   ,@PublishTime
                                   ,0)";
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(sql);
            command.Parameters.Add(DBHelper.CreateParamer("Content", DBHelper.getValue(knowledge.Content)));
            command.Parameters.Add(DBHelper.CreateParamer("Title", knowledge.Title));
            command.Parameters.Add(DBHelper.CreateParamer("Summary", knowledge.Summary));
            command.Parameters.Add(DBHelper.CreateParamer("PictureUrl", knowledge.PictureUrl));
            command.Parameters.Add(DBHelper.CreateParamer("Publisher", knowledge.Publisher));
            command.Parameters.Add(DBHelper.CreateParamer("PublisherID", knowledge.PublisherID));
            command.Parameters.Add(DBHelper.CreateParamer("PublishTime", knowledge.PublishTime));
            DBHelper.Instance.ExecuteNonQuery(command);
        }

        public static  CommonKnowledge FillContent(DataRow dr)
        {
            CommonKnowledge notice = new CommonKnowledge();
            notice.KnowledgeID = Convert.ToString(dr["KnowledgeID"]);

            if (!dr.IsNull("Content"))
            {
                notice.Content = Convert.ToString(dr["Content"]);
            }
            if (!dr.IsNull("Title"))
            {
                notice.Title = Convert.ToString(dr["Title"]);
            }
            if (!dr.IsNull("Summary"))
            {
                notice.Summary = Convert.ToString(dr["Summary"]);
            }
            if (!dr.IsNull("PictureUrl"))
            {
                notice.PictureUrl = Convert.ToString(dr["PictureUrl"]);
            }
         
            if (!dr.IsNull("Publisher"))
            {
                notice.Publisher = Convert.ToString(dr["Publisher"]);
            }
            if (!dr.IsNull("PublisherID"))
            {
                notice.PublisherID = Convert.ToString(dr["PublisherID"]);
            }
            if (!dr.IsNull("PublishTime"))
            {
                notice.PublishTime = Convert.ToDateTime(dr["PublishTime"]);
            }
            if (!dr.IsNull("IsDeleted"))
            {
                notice.IsDeleted = Convert.ToBoolean(dr["IsDeleted"]);
            }
            return notice;
        }

        public static DataSet ListAllKnowledge()
        {
            string sql = @"select *
	                                from SYS_Knowledges n 
                                where n.IsDeleted = 0
                                order by publishTime desc";
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(sql);
            DataSet ds = DBHelper.Instance.ExecuteDataSet(command);
            return ds;
        }

        public static DataSet ListTopKnowledge()
        {
            string sql = @"select top 5 *
	                                from SYS_Knowledges n 
                                where n.IsDeleted = 0 
                                order by publishTime desc";
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(sql);
            DataSet ds = DBHelper.Instance.ExecuteDataSet(command);
            return ds;
        }
    }
}
